package com.et114.components.osworkflow4Ibatis;

import com.ibatis.sqlmap.client.*;

import com.opensymphony.module.propertyset.PropertySet;
import com.opensymphony.module.propertyset.PropertySetManager;

import com.opensymphony.workflow.StoreException;
import com.opensymphony.workflow.query.Expression;
import com.opensymphony.workflow.query.FieldExpression;
import com.opensymphony.workflow.query.NestedExpression;
import com.opensymphony.workflow.query.WorkflowExpressionQuery;
import com.opensymphony.workflow.query.WorkflowQuery;
import com.opensymphony.workflow.spi.SimpleStep;
import com.opensymphony.workflow.spi.SimpleWorkflowEntry;
import com.opensymphony.workflow.spi.Step;
import com.opensymphony.workflow.spi.WorkflowEntry;
import com.opensymphony.workflow.spi.WorkflowStore;
import java.sql.*;
import java.util.*;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/*
 * @author guanhw
 */
public class IBatisWorkflowStore implements WorkflowStore {
	protected Log log = LogFactory.getLog( this.getClass( ) );
	protected SqlMapClient							sqlMapClient;
	protected boolean								closeConnWhenDone	= true;
	
	public void setSqlMapClient ( SqlMapClient sqlMapClient ) {
		this.sqlMapClient = sqlMapClient;
	}
	
	public SqlMapClient getSqlMapClient ( ) {
		return sqlMapClient;
	}
	
	public void setEntryState ( long id , int state ) throws StoreException {
		try {
			WorkflowEntryData data = new WorkflowEntryData ( );
			data.setId ( new Long( id ) );
			data.setState ( new Integer( state ) );
			getSqlMapClient ( ).update ( "updateWorkflowEntry" , data );
			log.debug ( "set wf entry state, id: " + id + ", state: " + state );
		} catch ( Exception e ) {
			throw new StoreException (
					"Unable to update state for workflow instance #" + id
							+ " to " + state , e );
		}
	}
	
	public PropertySet getPropertySet ( long entryId ) {
		HashMap args = new HashMap ( 1 );
		args.put ( "globalKey" , "os_" + entryId );
		args.put ( "sqlMapClient" , getSqlMapClient ( ) );
		
		return PropertySetManager.getInstance ( "ibatis" , args );
	}
	
	public boolean checkIfORExists ( NestedExpression nestedExpression ) {
		int numberOfExp = nestedExpression.getExpressionCount ( );
		if ( nestedExpression.getExpressionOperator ( ) == NestedExpression.OR ) {
			return true;
		}
		for ( int i = 0 ; i < numberOfExp ; i ++ ) {
			Expression expression = nestedExpression.getExpression ( i );
			if ( expression.isNested ( ) ) {
				NestedExpression nestedExp = ( NestedExpression ) expression;
				
				return checkIfORExists ( nestedExp );
			}
		}
		return false;
	}
	
	public Step createCurrentStep ( long entryId , int wfStepId , String owner ,
			Date startDate , Date dueDate , String status , long [ ] previousIds )
			throws StoreException {
		try {
			long id = createCurrentStep ( entryId , wfStepId , owner ,
					startDate , dueDate , status );
			addPreviousSteps ( id , previousIds );
			
			return new SimpleStep ( id , entryId , wfStepId , 0 , owner ,
					startDate , dueDate , null , status , previousIds , null );
		} catch ( Exception e ) {
			throw new StoreException (
					"Unable to create current step for workflow instance #"
							+ entryId , e );
		}
	}
	
	public WorkflowEntry createEntry ( String workflowName )
			throws StoreException {
		try {
			WorkflowEntryData data = new WorkflowEntryData ( );
			//long id = getNextEntrySequence ( );
			//data.setId ( new Long( id ) );
			data.setName ( workflowName );
			data.setState ( new Integer ( WorkflowEntry.CREATED )  );
			getSqlMapClient ( ).insert ( "insertWorkflowEntry" , data );
			log.debug ( "inserted new wf entry, id: " + data.getId ( )+ ", name: " + workflowName );
			return new SimpleWorkflowEntry ( data.getId ( ).longValue ( ) , workflowName , WorkflowEntry.CREATED );
		} catch ( Exception e ) {
			throw new StoreException ( "Error creating new workflow instance" , e );
		}
	}
	
	public List findCurrentSteps ( long entryId ) throws StoreException {
		try {
			List stepList = getSqlMapClient ( ).queryForList ( "selectCurrentStepByEntry" , new Long ( entryId ) );
			log.debug ( "get step by entry id: " + entryId + ", count: "	+ stepList.size ( ) );
			ArrayList currentSteps = new ArrayList ( );
			for ( int i = 0 ; i < stepList.size ( ) ; i ++ ) {
				WorkflowStepData stepData = ( WorkflowStepData ) stepList.get ( i );
				long id = stepData.getId ( ).longValue ( );
				int stepId = stepData.getStepId ( ).intValue ( );
				int actionId = stepData.getActionId ( ) == null ? 0 : stepData.getActionId ( ).intValue ( );
				String owner = stepData.getOwner ( );
				Date startDate = stepData.getStartDate ( );
				Date dueDate = stepData.getDueDate ( );
				Date finishDate = stepData.getFinishDate ( );
				String status = stepData.getStatus ( );
				String caller = stepData.getCaller ( );
				log.debug ( "step data, id: " + stepData.getId ( ) );
				
				List prevList = getSqlMapClient ( ).queryForList ( "selectCurrentPriviousStep" , stepData.getId ( )  );
				long [ ] prevIds = new long [ prevList.size ( ) ];
				log.debug ( "find prev steps, count: " + prevList.size ( ) );
				
				for ( int j = 0 ; j < prevList.size ( ) ; j ++ ) {
					PreviousStepData p = ( PreviousStepData ) prevList.get ( j );
					prevIds [ j ] = p.getPreviousId ( ).longValue ( );
				}
				
				SimpleStep step = new SimpleStep ( id , entryId , stepId ,
						actionId , owner , startDate , dueDate , finishDate ,
						status , prevIds , caller );
				currentSteps.add ( step );
			}
			
			return currentSteps;
		} catch ( Exception e ) {
			throw new StoreException (
					"Unable to locate current steps for workflow instance #"
							+ entryId , e );
		}
	}
	
	public WorkflowEntry findEntry ( long theEntryId ) throws StoreException {
		try {
			WorkflowEntryData data = ( WorkflowEntryData ) getSqlMapClient ( )
					.queryForObject ( "selectWorkflowEntry" ,
							new Long ( theEntryId ) );
			
			String workflowName = data.getName ( );
			int state = data.getState ( ).intValue ( );
			return new SimpleWorkflowEntry ( theEntryId , workflowName , state );
		} catch ( Exception e ) {
			throw new StoreException ( "Error finding workflow instance #"
					+ theEntryId );
		}
	}
	
	public List findHistorySteps ( long entryId ) throws StoreException {
		try {
			List stepList = getSqlMapClient ( ).queryForList (
					"selectHistoryStepByEntry" , new Long ( entryId ) );
			log.debug ( "select history step by entry id, count: "
					+ stepList.size ( ) );
			
			ArrayList currentSteps = new ArrayList ( );
			for ( int i = 0 ; i < stepList.size ( ) ; i ++ ) {
				WorkflowStepData stepData = ( WorkflowStepData ) stepList
						.get ( i );
				
				List prevList = getSqlMapClient ( ).queryForList (
						"selectHistoryPriviousStep" ,
						 stepData.getId ( )  );
				long [ ] prevIds = new long [ prevList.size ( ) ];
				
				for ( int j = 0 ; j < prevList.size ( ) ; j ++ ) {
					PreviousStepData p = ( PreviousStepData ) prevList.get ( j );
					prevIds [ j ] = p.getPreviousId ( ).longValue ( );
				}
				
				SimpleStep step = new SimpleStep ( stepData.getId ( ).longValue ( ) ,
						stepData.getEntryId ( ).longValue ( ) , stepData.getStepId ( ).intValue ( ) ,
						stepData.getActionId ( ).intValue ( ) , stepData.getOwner ( ) ,
						stepData.getStartDate ( ) , stepData.getDueDate ( ) ,
						stepData.getFinishDate ( ) , stepData.getStatus ( ) ,
						prevIds , stepData.getCaller ( ) );
				currentSteps.add ( step );
			}
			return currentSteps;
		} catch ( Exception e ) {
			throw new StoreException (
					"Unable to locate history steps for workflow instance #"
							+ entryId , e );
		}
	}
	
	public void init ( Map props ) {
	}
	
	public Step markFinished ( Step step , int actionId , Date finishDate ,
			String status , String caller ) throws StoreException {
		try {
			WorkflowStepData data = new WorkflowStepData ( );
			data.setStatus ( status );
			data.setActionId ( new Integer( actionId ) );
			data.setFinishDate ( finishDate );
			data.setCaller ( caller );
			data.setId ( new Long( step.getId ( ) ) );
			getSqlMapClient ( ).update ( "updateCurrentStep" , data );
			log.debug ( "current step updated" );
			
			SimpleStep theStep = ( SimpleStep ) step;
			theStep.setActionId ( actionId );
			theStep.setFinishDate ( finishDate );
			theStep.setStatus ( status );
			theStep.setCaller ( caller );
			
			return theStep;
		} catch ( Exception e ) {
			throw new StoreException ( "Unable to mark step finished for #"
					+ step.getEntryId ( ) , e );
		}
	}
	
	public void moveToHistory ( Step step ) throws StoreException {
		try {
			WorkflowStepData stepData = new WorkflowStepData ( );
			stepData.setId ( new Long( step.getId ( ) ) );
			stepData.setEntryId (  new Long( step.getEntryId ( ) ) );
			stepData.setStepId ( new Integer( step.getStepId ( ) ) );
			stepData.setActionId ( new Integer( step.getActionId ( ) ) );
			stepData.setOwner ( step.getOwner ( ) );
			stepData.setStartDate ( step.getStartDate ( ) );
			stepData.setFinishDate ( step.getFinishDate ( ) );
			stepData.setStatus ( step.getStatus ( ) );
			stepData.setCaller ( step.getCaller ( ) );
			getSqlMapClient ( ).insert ( "insertHistoryStep" , stepData );
			
			log.debug ( "inserted history step, id: " + step.getId ( ) );
			long [ ] previousIds = step.getPreviousStepIds ( );
			
			if ( ( previousIds != null ) && ( previousIds.length > 0 ) ) {
				log.debug ( "insert previous history step, count: "
						+ previousIds.length );
				
				for ( int i = 0 ; i < previousIds.length ; i ++ ) {
					PreviousStepData preData = new PreviousStepData ( );
					preData.setId ( new Long( step.getId ( ) ) );
					preData.setPreviousId ( new Long( previousIds [ i ] ) );
					getSqlMapClient ( ).insert ( "insertHistoryPriviousStep" ,
							preData );
				}
			}
			
			log.debug ( "delete current prev step: " + step.getId ( ) );
			getSqlMapClient ( ).delete ( "deleteCurrentPriviousStep" ,
					new Long ( step.getId ( ) ) );
			
			log.debug ( "delete current step: " + step.getId ( ) );
			getSqlMapClient ( ).delete ( "deleteCurrentStep" ,
					new Long ( step.getId ( ) ) );
		} catch ( Exception e ) {
			throw new StoreException (
					"Unable to move current step to history step for #"
							+ step.getEntryId ( ) , e );
		}
	}
	
	public List query ( WorkflowExpressionQuery e ) throws StoreException {
		// GURKAN;
		// If it is simple, call buildSimple()
		// SELECT DISTINCT(ENTRY_ID) FROM os_historystep WHERE FINISH_DATE < ?
		//
		// If it is nested, call doNestedNaturalJoin() if and only if the query
		// is
		// ANDed including nested-nestd queries
		// If OR exists in any query call buildNested()
		//
		// doNestedNaturalJoin()
		// This doNestedNaturalJoin() method improves performance of the queries
		// if and only if
		// the queries including nested queries are ANDed
		//
		// SELECT DISTINCT (a1.ENTRY_ID) AS retrieved
		// FROM os_currentstep AS a1 , os_currentstep AS a2 , os_currentstep AS
		// a3 , os_currentstep AS a4
		// WHERE ((a1.ENTRY_ID = a1.ENTRY_ID AND a1.ENTRY_ID = a2.ENTRY_ID) AND
		// (a2.ENTRY_ID = a3.ENTRY_ID AND a3.ENTRY_ID = a4.ENTRY_ID))
		// AND ( a1.OWNER = ? AND a2.STATUS != ? AND a3.OWNER = ? AND a4.STATUS
		// != ? )
		//
		// doNestedLeftJoin() //not used
		// For this method to work, order of queries is matter
		// This doNestedLeftJoin() method will generate the queries but it works
		// if and only if
		// the query is in correct order -- it is your luck
		// SELECT DISTINCT (a0.ENTRY_ID) AS retrieved FROM os_currentstep AS a0
		// LEFT JOIN os_currentstep a1 ON a0.ENTRY_ID = a1.ENTRY_ID
		//
		// LEFT JOIN os_currentstep a2 ON a1.ENTRY_ID = a2.ENTRY_ID
		// LEFT JOIN os_currentstep a3 ON a2.ENTRY_ID = a3.ENTRY_ID
		// WHERE a1.OWNER = ? AND (a2.STATUS = ? OR a3.OWNER = ?)
		//
		if ( log.isDebugEnabled ( ) ) {
			log.debug ( "Starting Query" );
		}
		
		Expression expression = e.getExpression ( );
		
		if ( log.isDebugEnabled ( ) ) {
			log.debug ( "Have all variables" );
		}
		
		if ( expression.isNested ( ) ) {
			NestedExpression nestedExp = ( NestedExpression ) expression;
			
			StringBuffer sel = new StringBuffer ( );
			StringBuffer columns = new StringBuffer ( );
			StringBuffer leftJoin = new StringBuffer ( );
			StringBuffer where = new StringBuffer ( );
			StringBuffer whereComp = new StringBuffer ( );
			StringBuffer orderBy = new StringBuffer ( );
			List values = new LinkedList ( );
			List queries = new LinkedList ( );
			
			String columnName;
			String selectString;
			
			// Expression is nested and see if the expresion has OR
			if ( checkIfORExists ( nestedExp ) ) {
				// For doNestedLeftJoin() uncomment these -- again order is
				// matter
				// and comment out last two lines where buildNested() is called
				//
				// columns.append("SELECT DISTINCT (");
				// columns.append("a0" + "." + stepEntryId);
				// columnName = "retrieved";
				// columns.append(") AS " + columnName);
				// columns.append(" FROM ");
				// columns.append(currentTable + " AS " + "a0");
				// where.append("WHERE ");
				// doNestedLeftJoin(e, nestedExp, leftJoin, where, values,
				// queries, orderBy);
				// selectString = columns.toString() + " " + leftJoin.toString()
				// + " " + where.toString() + " " + orderBy.toString();
				// System.out.println("LEFT JOIN ...");
				//
				//
				columnName = buildNested ( nestedExp , sel , values );
				selectString = sel.toString ( );
			} else {
				columns.append ( "SELECT DISTINCT (" );
				columns.append ( "a1.ENTRY_ID" );
				columnName = "retrieved";
				columns.append ( ") AS " + columnName );
				columns.append ( " FROM " );
				where.append ( "WHERE " );
				
				doNestedNaturalJoin ( e , nestedExp , columns , where ,
						whereComp , values , queries , orderBy );
				selectString = columns.toString ( ) + ' '
						+ leftJoin.toString ( ) + ' ' + where.toString ( )
						+ " AND ( " + whereComp.toString ( ) + " ) " + ' '
						+ orderBy.toString ( );
				
				// System.out.println("NATURAL JOIN ...");
			}
			
			// System.out.println("number of queries is      : " +
			// queries.size());
			// System.out.println("values.toString()         : " +
			// values.toString());
			// System.out.println("columnName                : " + columnName);
			// System.out.println("where                     : " + where);
			// System.out.println("whereComp                 : " + whereComp);
			// System.out.println("columns                   : " + columns);
			// System.out.println("Query is : " + selectString + "\n");
			return doExpressionQuery ( selectString , columnName , values );
		} else {
			// query is not empty ... it's a SIMPLE query
			// do what the old query did
			StringBuffer qry;
			List values = new LinkedList ( );
			
			qry = new StringBuffer ( );
			
			String columnName = buildSimple ( ( FieldExpression ) expression ,
					qry , values );
			
			if ( e.getSortOrder ( ) != WorkflowExpressionQuery.SORT_NONE ) {
				qry.append ( " ORDER BY " );
				
				if ( e.getOrderBy ( ) != 0 ) {
					String fName = fieldName ( e.getOrderBy ( ) );
					
					qry.append ( fName );
					
					// To help w/ MySQL and Informix, you have to include the
					// column in the query
					String current = qry.toString ( );
					String entry = current.substring ( 0 , current
							.indexOf ( columnName ) )
							+ columnName + "), " + fName + ' ';
					entry += current.substring ( current.indexOf ( columnName )
							+ columnName.length ( ) + 1 );
					
					qry = new StringBuffer ( entry );
					
					if ( e.getSortOrder ( ) == WorkflowExpressionQuery.SORT_DESC ) {
						qry.append ( " DESC" );
					} else {
						qry.append ( " ASC" );
					}
				} else {
					qry.append ( columnName );
				}
			}
			
			// System.out.println("Query is: " + qry.toString());
			return doExpressionQuery ( qry.toString ( ) , columnName , values );
		}
	}
	
	public List query ( WorkflowQuery query ) throws StoreException {
		List results = null;
		
		// going to try to do all the comparisons in one query
		String sel;
		String table;
		
		int qtype = query.getType ( );
		
		if ( qtype == 0 ) { // then not set, so look in sub queries
			// todo: not sure if you would have a query that would look in both
			// old and new, if so, i'll have to change this - TR
			// but then again, why are there redundant tables in the first
			// place? the data model should probably change
			
			if ( query.getLeft ( ) != null ) {
				qtype = query.getLeft ( ).getType ( );
			}
		}
		
		if ( qtype == WorkflowQuery.CURRENT ) {
			table = "os_currentstep";
		} else {
			table = "os_historystep";
		}
		
		sel = "SELECT DISTINCT(ENTRY_ID) AS retrieved FROM " + table
				+ " WHERE ";
		sel += queryWhere ( query );
		
		try {
			results = getSqlMapClient ( ).queryForList ( "queryWorkflowEntry" ,
					sel );
		} catch ( Exception ex ) {
			throw new StoreException ( "SQL Exception in query: "
					+ ex.getMessage ( ) );
		}
		
		return results;
	}
	

	
	protected void addPreviousSteps ( long id , long [ ] previousIds )
			throws StoreException {
		if ( ( previousIds != null ) && ( previousIds.length > 0 ) ) {
			if ( ! ( ( previousIds.length == 1 ) && ( previousIds [ 0 ] == 0 ) ) ) {
				try {
					for ( int i = 0 ; i < previousIds.length ; i ++ ) {
						PreviousStepData data = new PreviousStepData ( );
						data.setId ( new Long( id ) );
						data.setPreviousId ( new Long( previousIds [ i ] ) );
						getSqlMapClient ( ).insert (
								"insertCurrentPriviousStep" , data );
					}
				} catch ( Exception e ) {
					throw new StoreException ( "Exception in add prev steps" ,
							e );
				}
			}
		}
	}
	
	protected void cleanup ( Connection connection , Statement statement ,
			ResultSet result ) {
		if ( result != null ) {
			try {
				result.close ( );
			} catch ( SQLException ex ) {
				log.error ( "Error closing resultset" , ex );
			}
		}
		
		if ( statement != null ) {
			try {
				statement.close ( );
			} catch ( SQLException ex ) {
				log.error ( "Error closing statement" , ex );
			}
		}
		
		if ( ( connection != null ) && closeConnWhenDone ) {
			try {
				connection.close ( );
			} catch ( SQLException ex ) {
				log.error ( "Error closing connection" , ex );
			}
		}
	}
	
	protected long createCurrentStep ( long entryId , int wfStepId ,
			String owner , Date startDate , Date dueDate , String status )
			throws StoreException {
		try {
			WorkflowStepData data = new WorkflowStepData ( );
			//long id = getNextStepSequence ( );
			//log.debug ( "get new step id: " + id );
			//data.setId ( new Long( id ) );
			data.setEntryId ( new Long( entryId ) );
			data.setStepId ( new Integer( wfStepId ) );
			data.setOwner ( owner );
			data.setStartDate ( startDate );
			data.setDueDate ( dueDate );
			data.setStatus ( status );
			this.getSqlMapClient ( ).insert ( "insertCurrentStep" , data );
			
			return data.getId ( ).longValue ( );
		} catch ( Exception e ) {
			throw new StoreException ( "Exception in create current step" , e );
		}
		
	}
	
	// //////////METHOD #3 OF 3 //////////////////
	// //////// ...gur; ////////////////////
	// kardes
	void doNestedNaturalJoin ( WorkflowExpressionQuery e ,
			NestedExpression nestedExpression , StringBuffer columns ,
			StringBuffer where , StringBuffer whereComp , List values ,
			List queries , StringBuffer orderBy ) { // throws StoreException {
	
		Object value;
		int currentExpField;
		
		int numberOfExp = nestedExpression.getExpressionCount ( );
		
		for ( int i = 0 ; i < numberOfExp ; i ++ ) { // ori
		
			// for (i = numberOfExp; i > 0; i--) { //reverse 1 of 3
			Expression expression = nestedExpression.getExpression ( i ); // ori
			
			// Expression expression = nestedExpression.getExpression(i - 1);
			// //reverse 2 of 3
			if ( ! ( expression.isNested ( ) ) ) {
				FieldExpression fieldExp = ( FieldExpression ) expression;
				
				FieldExpression fieldExpBeforeCurrent;
				queries.add ( expression );
				
				int queryId = queries.size ( );
				
				if ( queryId > 1 ) {
					columns.append ( " , " );
				}
				
				// do; OS_CURRENTSTEP AS a1 ....
				if ( fieldExp.getContext ( ) == FieldExpression.CURRENT_STEPS ) {
					columns.append ( "os_currentstep AS " + 'a' + queryId );
				} else if ( fieldExp.getContext ( ) == FieldExpression.HISTORY_STEPS ) {
					columns.append ( "os_historystep AS " + 'a' + queryId );
				} else {
					columns.append ( "os_wfentry AS " + 'a' + queryId );
				}
				
				// /////// beginning of WHERE JOINS/s :
				// //////////////////////////////////////////
				// do for first query; a1.ENTRY_ID = a1.ENTRY_ID
				if ( queryId == 1 ) {
					where.append ( "a1.ENTRY_ID" );
					where.append ( " = " );
					
					if ( fieldExp.getContext ( ) == FieldExpression.CURRENT_STEPS ) {
						where.append ( "a" + queryId + ".ENTRY_ID" );
					} else if ( fieldExp.getContext ( ) == FieldExpression.HISTORY_STEPS ) {
						where.append ( "a" + queryId + ".ENTRY_ID" );
					} else {
						where.append ( "a" + queryId + ".ID" );
					}
				}
				
				// do; a1.ENTRY_ID = a2.ENTRY_ID
				if ( queryId > 1 ) {
					fieldExpBeforeCurrent = ( FieldExpression ) queries
							.get ( queryId - 2 );
					
					if ( fieldExpBeforeCurrent.getContext ( ) == FieldExpression.CURRENT_STEPS ) {
						where.append ( "a" + ( queryId - 1 ) + ".ENTRY_ID" );
					} else if ( fieldExpBeforeCurrent.getContext ( ) == FieldExpression.HISTORY_STEPS ) {
						where.append ( "a" + ( queryId - 1 ) + ".ENTRY_ID" );
					} else {
						where.append ( "a" + ( queryId - 1 ) + ".ID" );
					}
					
					where.append ( " = " );
					
					if ( fieldExp.getContext ( ) == FieldExpression.CURRENT_STEPS ) {
						where.append ( "a" + queryId + ".ENTRY_ID" );
					} else if ( fieldExp.getContext ( ) == FieldExpression.HISTORY_STEPS ) {
						where.append ( "a" + queryId + ".ENTRY_ID" );
					} else {
						where.append ( "a" + queryId + ".ID" );
					}
				}
				
				// /////// end of LEFT JOIN : "LEFT JOIN OS_CURRENTSTEP a1 ON
				// a0.ENTRY_ID = a1.ENTRY_ID
				//
				// ////// BEGINNING OF WHERE clause
				// //////////////////////////////////////////////////
				value = fieldExp.getValue ( );
				currentExpField = fieldExp.getField ( );
				
				// if the Expression is negated and FieldExpression is "EQUALS",
				// we need to negate that FieldExpression
				if ( expression.isNegate ( ) ) {
					// do ; a2.STATUS !=
					whereComp.append ( "a" + queryId + '.'
							+ fieldName ( fieldExp.getField ( ) ) );
					
					switch ( fieldExp.getOperator ( ) ) { // WHERE a1.STATUS !=
						case FieldExpression.EQUALS :

							if ( value == null ) {
								whereComp.append ( " IS NOT " );
							} else {
								whereComp.append ( " != " );
							}
							
							break;
						
						case FieldExpression.NOT_EQUALS :

							if ( value == null ) {
								whereComp.append ( " IS " );
							} else {
								whereComp.append ( " = " );
							}
							
							break;
						
						case FieldExpression.GT :
							whereComp.append ( " < " );
							
							break;
						
						case FieldExpression.LT :
							whereComp.append ( " > " );
							
							break;
						
						default :
							whereComp.append ( " != " );
							
							break;
					}
					
					switch ( currentExpField ) {
						case FieldExpression.START_DATE :
						case FieldExpression.FINISH_DATE :
							values.add ( new Timestamp (
									( ( java.util.Date ) value ).getTime ( ) ) );
							
							break;
						
						default :

							if ( value == null ) {
								values.add ( null );
							} else {
								values.add ( value );
							}
							
							break;
					}
				} else {
					// do; a1.OWNER =
					whereComp.append ( "a" + queryId + '.'
							+ fieldName ( fieldExp.getField ( ) ) );
					
					switch ( fieldExp.getOperator ( ) ) { // WHERE
															// a2.FINISH_DATE <
						case FieldExpression.EQUALS :

							if ( value == null ) {
								whereComp.append ( " IS " );
							} else {
								whereComp.append ( " = " );
							}
							
							break;
						
						case FieldExpression.NOT_EQUALS :

							if ( value == null ) {
								whereComp.append ( " IS NOT " );
							} else {
								whereComp.append ( " <> " );
							}
							
							break;
						
						case FieldExpression.GT :
							whereComp.append ( " > " );
							
							break;
						
						case FieldExpression.LT :
							whereComp.append ( " < " );
							
							break;
						
						default :
							whereComp.append ( " = " );
							
							break;
					}
					
					switch ( currentExpField ) {
						case FieldExpression.START_DATE :
						case FieldExpression.FINISH_DATE :
							values.add ( new Timestamp (
									( ( java.util.Date ) value ).getTime ( ) ) );
							
							break;
						
						default :

							if ( value == null ) {
								values.add ( null );
							} else {
								values.add ( value );
							}
							
							break;
					}
				}
				
				// do; a1.OWNER = ? ... a2.STATUS != ?
				whereComp.append ( " ? " );
				
				// ////// END OF WHERE
				// clause////////////////////////////////////////////////////////////
				if ( ( e.getSortOrder ( ) != WorkflowExpressionQuery.SORT_NONE )
						&& ( e.getOrderBy ( ) != 0 ) ) {
					System.out.println ( "ORDER BY ; queries.size() : "
							+ queries.size ( ) );
					orderBy.append ( " ORDER BY " );
					orderBy
							.append ( "a1" + '.'
									+ fieldName ( e.getOrderBy ( ) ) );
					
					if ( e.getSortOrder ( ) == WorkflowExpressionQuery.SORT_ASC ) {
						orderBy.append ( " ASC" );
					} else if ( e.getSortOrder ( ) == WorkflowExpressionQuery.SORT_DESC ) {
						orderBy.append ( " DESC" );
					}
				}
			} else {
				NestedExpression nestedExp = ( NestedExpression ) expression;
				
				where.append ( '(' );
				
				doNestedNaturalJoin ( e , nestedExp , columns , where ,
						whereComp , values , queries , orderBy );
				
				where.append ( ')' );
			}
			
			// add AND or OR clause between the queries
			if ( i < ( numberOfExp - 1 ) ) { // ori
			
				// if (i > 1) { //reverse 3 of 3
				if ( nestedExpression.getExpressionOperator ( ) == NestedExpression.AND ) {
					where.append ( " AND " );
					whereComp.append ( " AND " );
				} else {
					where.append ( " OR " );
					whereComp.append ( " OR " );
				}
			}
		}
	}
	
	private String getInitProperty ( Map props , String strName ,
			String strDefault ) {
		Object o = props.get ( strName );
		
		if ( o == null ) {
			return strDefault;
		}
		
		return ( String ) o;
	}
	
	private String buildNested ( NestedExpression nestedExpression ,
			StringBuffer sel , List values ) {
		sel.append ( "SELECT DISTINCT(" );
		
		// Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP
		// instead
		// sel.append(entryId);
		sel.append ( "ENTRY_ID AS retrieved" );
		sel.append ( ") FROM " );
		
		// Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP
		// instead
		// sel.append(entryTable);
		sel.append ( "os_currentstep" );
		
		if ( log.isDebugEnabled ( ) ) {
			log.debug ( "Thus far, query is: " + sel.toString ( ) );
		}
		
		for ( int i = 0 ; i < nestedExpression.getExpressionCount ( ) ; i ++ ) {
			Expression expression = nestedExpression.getExpression ( i );
			
			if ( i == 0 ) {
				sel.append ( " WHERE " );
			} else {
				if ( nestedExpression.getExpressionOperator ( ) == NestedExpression.AND ) {
					sel.append ( " AND " );
				} else {
					sel.append ( " OR " );
				}
			}
			
			if ( expression.isNegate ( ) ) {
				sel.append ( " NOT " );
			}
			
			// Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP
			// instead
			// sel.append(entryId);
			sel.append ( "ENTRY_ID" );
			sel.append ( " IN (" );
			
			if ( expression.isNested ( ) ) {
				this.buildNested ( ( NestedExpression ) nestedExpression
						.getExpression ( i ) , sel , values );
			} else {
				FieldExpression sub = ( FieldExpression ) nestedExpression
						.getExpression ( i );
				this.buildSimple ( sub , sel , values );
			}
			
			sel.append ( ')' );
		}
		
		// Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP
		// instead
		// return (entryId);
		return ( "ENTRY_ID" );
	}
	
	private String buildSimple ( FieldExpression fieldExpression ,
			StringBuffer sel , List values ) {
		String table;
		String columnName;
		
		if ( fieldExpression.getContext ( ) == FieldExpression.CURRENT_STEPS ) {
			table = "os_currentstep";
			columnName = "ENTRY_ID";
		} else if ( fieldExpression.getContext ( ) == FieldExpression.HISTORY_STEPS ) {
			table = "os_historystep";
			columnName = "ENTRY_ID";
		} else {
			table = "os_wfentry";
			columnName = "ID";
		}
		
		sel.append ( "SELECT DISTINCT(" );
		sel.append ( columnName );
		sel.append ( ") FROM " );
		sel.append ( table );
		sel.append ( " WHERE " );
		queryComparison ( fieldExpression , sel , values );
		
		return columnName;
	}
	
	private List doExpressionQuery ( String sel , String columnName ,
			List values ) throws StoreException {
		try {
			List results = getSqlMapClient ( ).queryForList ( "queryWorkflowEntry" , sel );
			return results;
		} catch ( Exception ex ) {
			throw new StoreException ( "SQL Exception in query: "
					+ ex.getMessage ( ) );
		}
	}
	
	private static String escape ( String s ) {
		StringBuffer sb = new StringBuffer ( s );
		
		char c;
		char [ ] chars = s.toCharArray ( );
		
		for ( int i = 0 ; i < chars.length ; i ++ ) {
			c = chars [ i ];
			
			switch ( c ) {
				case '\'' :
					sb.insert ( i , '\'' );
					i ++ ;
					
					break;
				
				case '\\' :
					sb.insert ( i , '\\' );
					i ++ ;
			}
		}
		
		return sb.toString ( );
	}
	
	private String fieldName ( int field ) {
		switch ( field ) {
			case FieldExpression.ACTION : // actionId
				return "ACTION_ID";
				
			case FieldExpression.CALLER :
				return "CALLER";
				
			case FieldExpression.FINISH_DATE :
				return "FINISH_DATE";
				
			case FieldExpression.OWNER :
				return "OWNER";
				
			case FieldExpression.START_DATE :
				return "START_DATE";
				
			case FieldExpression.STEP : // stepId
				return "STEP_ID";
				
			case FieldExpression.STATUS :
				return "STATUS";
				
			case FieldExpression.STATE :
				return "STATE";
				
			case FieldExpression.NAME :
				return "NAME";
				
			case FieldExpression.DUE_DATE :
				return "DUE_DATE";
				
			default :
				return "1";
		}
	}
	
	private String queryComparison ( WorkflowQuery query ) {
		Object value = query.getValue ( );
		int operator = query.getOperator ( );
		int field = query.getField ( );
		
		// int type = query.getType();
		String oper;
		
		switch ( operator ) {
			case WorkflowQuery.EQUALS :
				oper = " = ";
				
				break;
			
			case WorkflowQuery.NOT_EQUALS :
				oper = " <> ";
				
				break;
			
			case WorkflowQuery.GT :
				oper = " > ";
				
				break;
			
			case WorkflowQuery.LT :
				oper = " < ";
				
				break;
			
			default :
				oper = " = ";
		}
		
		String left = fieldName ( field );
		String right;
		
		if ( value != null ) {
			right = '\'' + escape ( value.toString ( ) ) + '\'';
		} else {
			right = "null";
		}
		
		return left + oper + right;
	}
	
	/**
	 * Method queryComparison
	 * 
	 * @param expression
	 *            a FieldExpression
	 * @param sel
	 *            a StringBuffer
	 * 
	 */
	private void queryComparison ( FieldExpression expression ,
			StringBuffer sel , List values ) {
		Object value = expression.getValue ( );
		int operator = expression.getOperator ( );
		int field = expression.getField ( );
		
		String oper;
		
		switch ( operator ) {
			case FieldExpression.EQUALS :

				if ( value == null ) {
					oper = " IS ";
				} else {
					oper = " = ";
				}
				
				break;
			
			case FieldExpression.NOT_EQUALS :

				if ( value == null ) {
					oper = " IS NOT ";
				} else {
					oper = " <> ";
				}
				
				break;
			
			case FieldExpression.GT :
				oper = " > ";
				
				break;
			
			case FieldExpression.LT :
				oper = " < ";
				
				break;
			
			default :
				oper = " = ";
		}
		
		String left = fieldName ( field );
		String right = "?";
		
		switch ( field ) {
			case FieldExpression.FINISH_DATE :
				values.add ( new Timestamp ( ( ( Date ) value ).getTime ( ) ) );
				
				break;
			
			case FieldExpression.START_DATE :
				values.add ( new Timestamp ( ( ( Date ) value ).getTime ( ) ) );
				
				break;
			
			case FieldExpression.DUE_DATE :
				values.add ( new Timestamp ( ( ( Date ) value ).getTime ( ) ) );
				
				break;
			
			default :

				if ( value == null ) {
					right = "null";
				} else {
					values.add ( value );
				}
		}
		
		sel.append ( left );
		sel.append ( oper );
		sel.append ( right );
	}
	
	private String queryWhere ( WorkflowQuery query ) {
		if ( query.getLeft ( ) == null ) {
			// leaf node
			return queryComparison ( query );
		} else {
			int operator = query.getOperator ( );
			WorkflowQuery left = query.getLeft ( );
			WorkflowQuery right = query.getRight ( );
			
			switch ( operator ) {
				case WorkflowQuery.AND :
					return '(' + queryWhere ( left ) + " AND "
							+ queryWhere ( right ) + ')';
					
				case WorkflowQuery.OR :
					return '(' + queryWhere ( left ) + " OR "
							+ queryWhere ( right ) + ')';
					
				case WorkflowQuery.XOR :
					return '(' + queryWhere ( left ) + " XOR "
							+ queryWhere ( right ) + ')';
			}
		}
		
		return ""; // not sure if we should throw an exception or how this
					// should be handled
	}
	
}